﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace NeoseekerCrawler
{
    class Program
    {
        static void Main(string[] args)
        {
            string content = string.Empty;

            using (WebClient client = new WebClient())
            {
                content = client.DownloadString("http://www.neoseeker.com/Games/title/PS3/all.html?p=2");
                content = Regex.Match(content, "<table width=100% cellspacing=\"1\" cellpadding=\"3\" border=\"0\" class=\"alphatitlelist\">(.|\n)*?</table>").Value;
            }

            MatchCollection tr_collection = Regex.Matches(content, "<tr(.|\n)*?>(.|\n)*?</tr>");

            List<string> titles_urls = new List<string>();

            foreach (Match tr in tr_collection)
            {
                string line = Regex.Match(tr.Value, "<strong><a(.|\n)*?>(.|\n)*?</a></strong>").Value;
                titles_urls.Add(line);
            }

            foreach (string title in titles_urls)
            {
                string nome = Regex.Replace(title, "<(.|\n)*?>", "").Replace("'", "&rsquo;");
                nome = nome.TrimStart(new char[] { ' ' });
                string url_neo = Regex.Match(title, @"((https?):((//)|(\\\\))+[\w\d:#@%/;$()~_?\+-=\\\.&]*)").Value;

                if (!string.IsNullOrEmpty(nome))
                {
                    string sql = "INSERT INTO jogos (nome, url_info_spider) VALUES ('" + nome + "', '" + url_neo + "')";
                    string sql_existe = "SELECT * FROM jogos WHERE nome = '" + nome + "';";

                    if (!existe(sql_existe))
                    {
                        Console.WriteLine(nome + " - NOVO");
                        gravar(sql);
                    }
                    else
                        Console.WriteLine(nome + " - EXISTE");
                }
            }

            Console.WriteLine("Terminou!");
            Console.ReadLine();
        }

        public static void gravar(string commandText)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CONN_STRING"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.Connection = connection;
                    command.ExecuteNonQuery();
                }
            }
        }

        public static bool existe(string commandText)
        {
            bool bExiste = false;

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CONN_STRING"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand())
                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.Connection = connection;
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                            bExiste = true;
                    }
                }
            }

            return bExiste;
        }
    }
}
